using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Security;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Timers;
using System.Web;
using System.Web.Mail;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;

namespace Halide
{
	/// <summary>
	/// The OleDbSmartReader class provides a more powerful, flexible OLEDB reader class.
	/// </summary>
	/// <example>
	/// Instantiate the class as:
	/// <code>
	/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
	/// </code>
	/// </example>
	public class OleDbSmartReader
	{
		private static string _version = "2007.11.06A";

		/// <summary>
		/// Return the version number of the class; read-only.
		/// </summary>
		public static string Version
		{
			get
			{
				return _version;
			}
		}

        private OleDbConnection cn;
		private OleDbDataReader dr;
		private OleDbCommand cmd;

		/// <summary>
		/// Number of rows that have been read.
		/// </summary>
		public int RowsRead = 0;

		/// <summary>
		/// Instantiate the class by passing a command string.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// </code>
		/// </example>
		/// <param name="CommandText">A command to execute.</param>
		/// <param name="connString">Connection string to use for OleDb connection.</param>
		public OleDbSmartReader(string CommandText, string connString)
		{
			cn = new OleDbConnection(connString);
			cn.Open();
			cmd = new OleDbCommand();
			cmd.Connection = cn;
			cmd.CommandText = CommandText;
			dr = cmd.ExecuteReader();
		}

		/// <summary>
		/// Class closes any open readers upon destruction.
		/// </summary>
		~OleDbSmartReader()
		{
			Close();
		}

		/// <summary>
		/// Close a previously opened OleDbSmartReader object. You must close an opened OleDbSmartReader object when finished using it!
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Close();
		/// </code>
		/// </example>
		/// <returns>Nothing.</returns>
		public void Close()
		{
			try
			{
				cn.Dispose();
				cmd.Dispose();
			}
			catch { }
		}

		/// <summary>
		/// Read the first (or next) row in the query.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// while (reader.Read())
		/// {
		///		...
		///	}
		///	reader.Close();
		/// </code>
		/// </example>
		/// <returns>true if read, false if end of data.</returns>
		public bool Read()
		{
			bool output;
			output = dr.Read();
			if (output) RowsRead++;
			return output;
		}

		/// <summary>
		/// Switch to the next result set; for use with SQL statements that return multiple (compound) recordsets.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.NextResult();
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <returns>true if result set is switched, false if not.</returns>
		public bool NextResult()
		{
			return dr.NextResult();
		}

		/// <summary>
		/// Shorthand for the GetString method. Returns a record as a string by name.
		/// </summary>
		/// <value>
		/// Record value as a string.
		/// </value>
		public string this[string ColumnName]
		{
			get
			{
				return GetString(ColumnName);
			}
		}

		/// <summary>
		/// Shorthand for the GetString method. Returns a record as a string by column number.
		/// </summary>
		/// <value>
		/// Record value as a string.
		/// </value>
		public string this[int ColumnIndex]
		{
			get
			{
				return GetString(ColumnIndex);
			}
		}

		/// <summary>
		/// Read a column value in as a DateTime object.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// DateTime reader.GetDateTime("thedate");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>DateTime object.</returns>
		public DateTime GetDateTime(string ColumnName)
		{
			DateTime output = new DateTime(1900, 1, 1);
			int ord = dr.GetOrdinal(ColumnName);

			try
			{
				output = dr.GetDateTime(ord);
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in as a string formatted as a date.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetDate("thedate");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve</param>
		/// <returns>A string with the column value as a date.</returns>
		public string GetDate(string ColumnName)
		{
			string output = string.Empty;

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = "";
				else output = dr.GetDateTime(ord).ToString("M/d/yyyy");
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in as a string formatted as a time.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetTime("thedate");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>A string with the column value as a time.</returns>
		public string GetTime(string ColumnName)
		{
			string output = string.Empty;

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = "";
				else output = dr.GetDateTime(ord).ToString("h:mm tt");
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in as an integer.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// int result = reader.GetInt("ID");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>An integer.</returns>
		public int GetInt(string ColumnName)
		{
			int output = 0;

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = 0;
				else output = dr.GetInt32(ord);
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Retrieve the SQL data type name of a specified column (eg. "string", "int", "tinyint", et al.).
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetDataTypeName(0);
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnIndex">Number of the column.</param>
		/// <returns>A string.</returns>
		public string GetDataTypeName(int ColumnIndex)
		{
			string output = string.Empty;

			try
			{
				output = dr.GetDataTypeName(ColumnIndex);
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Retrieve the SQL data type name of a specified column (eg. "string", "int", "tinyint", et al.).
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetDataTypeName("ID");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of the column.</param>
		/// <returns>A string.</returns>
		public string GetDataTypeName(string ColumnName)
		{
			return GetDataTypeName(dr.GetOrdinal(ColumnName));
		}

		/// <summary>
		/// Read a column value in as a double.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// double result = reader.GetDouble("ID");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>A double.</returns>
		public double GetDouble(string ColumnName)
		{
			double output = 0;

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = 0;
				else output = dr.GetDouble(ord);
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in as a float.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// float result = reader.GetFloat("ID");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>A float.</returns>
		public float GetFloat(string ColumnName)
		{
			float output = 0;

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = 0;
				else output = dr.GetFloat(ord);
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in as a string, by its column number.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetString(0);
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnIndex">Number of column to retrieve.</param>
		/// <returns>A string.</returns>
		public string GetString(int ColumnIndex)
		{
			string output = string.Empty;

			try
			{
				if (dr.IsDBNull(ColumnIndex)) output = "";
				else output = dr[ColumnIndex].ToString();
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in as a string, by its column number.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetString("ID");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Number of column to retrieve.</param>
		/// <returns>A string.</returns>
		public string GetString(string ColumnName)
		{
			return GetString(dr.GetOrdinal(ColumnName));
		}

		/// <summary>
		/// Read a column value in as a boolean, by its column name.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// bool result = reader.GetBoolean("ID");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>A boolean.</returns>
		public bool GetBoolean(string ColumnName)
		{
			bool output = false;

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = false;
				else output = dr.GetBoolean(ord);
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Read a column value in and process as "Yes", "No", or "n/a" using the GetBoolean method.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.GetYesNo("choice");
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnName">Name of column to retrieve.</param>
		/// <returns>A string.</returns>
		public string GetYesNo(string ColumnName)
		{
			string output = "n/a";

			try
			{
				int ord = dr.GetOrdinal(ColumnName);

				if (dr.IsDBNull(ord)) output = "n/a";
				else if (dr.GetBoolean(ord)) output = "Yes";
				else output = "No";
			}
			catch
			{ }

			return output;
		}

		/// <summary>
		/// Get a column name by its number.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// string result = reader.ColumnName(0);
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <param name="ColumnNumber">Number of column to retrieve.</param>
		/// <returns>A string.</returns>
		public string ColumnName(int ColumnNumber)
		{
			return dr.GetName(ColumnNumber);
		}

		/// <summary>
		/// Returns true if there are rows to return.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.OleDbSmartReader reader = new Halide.OleDbSmartReader("select * from test", "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\file.mdb;");
		/// reader.Read();
		/// bool result = reader.HasRows;
		/// ...
		/// reader.Close();
		/// </code>
		/// </example>
		/// <value>A boolean representing the availability of rows to read.</value>
		public bool HasRows
		{
			get { return dr.HasRows; }
		}

		/// <summary>
		/// Returns the number of fields in a recordset.
		/// </summary>
		/// <value>An integer.</value>
		public int FieldCount
		{
			get { return dr.FieldCount; }
		}

		// End of OleDbSmartReader class
	}

	// End of namespace
}
